package com.mlt.back.dao.impl;
import com.mlt.back.beans.Manager;
import com.mlt.back.beans.Role;
import com.mlt.back.dao.ManagerDao;
import com.mlt.back.utils.DBUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class ManagerDaoImpl implements ManagerDao {
    @Override
    public int insert(Manager mgr) {
        int count=0;
        Connection conn=null;
        PreparedStatement ps=null;
        try {
            conn=DBUtil.getConnection();
            String sql="insert into t_managers(login_name,login_pwd,mgr_gender,mgr_tel,mgr_email,create_time) values(?,?,?,?,?,?)";
            ps=conn.prepareStatement(sql);
            ps.setString(1,mgr.getLogin_name());
            ps.setString(2,mgr.getLogin_pwd());
            ps.setString(3,mgr.getMgr_gender());
            ps.setString(4,mgr.getMgr_tel());
            ps.setString(5, mgr.getMgr_email());
            ps.setDate(6,mgr.getCreate_time());
            count=ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(null,ps,null);
        }
        return count;
    }

    @Override
    public void update(Manager mgr) {
        Connection conn=null;
        PreparedStatement ps=null;
        try {
            conn=DBUtil.getConnection();
            String sql="update t_managers set login_name=?,login_pwd=?,mgr_gender=?,mgr_tel=?,mgr_email=? where mgr_id=?";
            ps=conn.prepareStatement(sql);
            ps.setString(1,mgr.getLogin_name());
            ps.setString(2,mgr.getLogin_pwd());
            ps.setString(3,mgr.getMgr_gender());
            ps.setString(4,mgr.getMgr_tel());
            ps.setString(5, mgr.getMgr_email());
            ps.setString(6,mgr.getMgr_id());
            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(conn,ps,null);
        }
    }

    @Override
    public Manager selectByLoginName(String login_name) {
        Connection conn=null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        Manager manager=new Manager();
        try {
            conn= DBUtil.getConnection();
            String sql="select mgr_id,login_pwd,mgr_name,mgr_gender,mgr_tel,mgr_email,mgr_qq,create_time from t_managers where login_name=?";
            ps=conn.prepareStatement(sql);
            ps.setString(1,login_name);
            rs=ps.executeQuery();
            if (rs.next()){
                //将取到的值存入manager对象
                manager.setMgr_id(rs.getString("mgr_id"));
                manager.setLogin_name(login_name);
                manager.setLogin_pwd(rs.getString("login_pwd"));
                manager.setMgr_name(rs.getString("mgr_name"));
                manager.setMgr_gender(rs.getString("mgr_gender"));
                manager.setMgr_tel(rs.getString("mgr_tel"));
                manager.setMgr_email(rs.getString("mgr_email"));
                manager.setMgr_qq(rs.getString("mgr_qq"));
                manager.setCreate_time(rs.getDate("create_time"));
            }else {
                return null;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(null,ps,rs);
        }
        return manager;
    }

    @Override
    public String doCheck(String name, String value) {
        String findName=null;
        Connection conn=null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        Manager manager=new Manager();
        try {
            conn= DBUtil.getConnection();
            String sql=null;
            if ("login_name".equals(name)){
                sql="select login_name from t_managers where login_name=?";
            }
            if ("mgr_tel".equals(name)){
                sql="select mgr_tel from t_managers where mgr_tel=?";
            }
            if ("mgr_email".equals(name)){
                sql="select mgr_email from t_managers where mgr_email=?";
            }
            ps= conn.prepareStatement(sql);
            ps.setString(1,value);
            rs=ps.executeQuery();
            if (rs.next()){
                findName=rs.getString(name);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(conn,ps,rs);
        }
        return findName;
    }

    @Override
    public List<Manager> selectByRole(String role_id, int page) {
        List<Manager> managerList =new ArrayList<>();
        Connection conn=null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        try {
            String sql="";
            conn=DBUtil.getConnection();
            if (role_id==null) {
                /*sql = "select m.mgr_id mgr_id,login_pwd,login_name,mgr_name,mgr_gender,mgr_tel,mgr_email,mgr_qq,create_time,r.role_id role_id,role_name,role_desc from t_managers m join t_mgr_role mr on m.mgr_id=mr.mgr_id join t_roles r on mr.role_id=r.role_id order by mgr_id asc limit ?,10";
                ps=conn.prepareStatement(sql);
                ps.setInt(1,page);*/
                sql = "select m.mgr_id mgr_id,login_pwd,login_name,mgr_name,mgr_gender,mgr_tel,mgr_email,mgr_qq,create_time,r.role_id role_id,role_name,role_desc from t_managers m join t_mgr_role mr on m.mgr_id=mr.mgr_id join t_roles r on mr.role_id=r.role_id order by mgr_id asc";
                ps=conn.prepareStatement(sql);
            }else {
                /*sql = "select m.mgr_id mgr_id,login_pwd,login_name,mgr_name,mgr_gender,mgr_tel,mgr_email,mgr_qq,create_time,r.role_id role_id,role_name,role_desc from t_managers m join t_mgr_role mr on m.mgr_id=mr.mgr_id join t_roles r on mr.role_id=r.role_id where mr.role_id=? order by mgr_id asc limit ?,10";
                ps=conn.prepareStatement(sql);
                ps.setString(1,role_id);
                ps.setInt(2,page);*/
                sql = "select m.mgr_id mgr_id,login_pwd,login_name,mgr_name,mgr_gender,mgr_tel,mgr_email,mgr_qq,create_time,r.role_id role_id,role_name,role_desc from t_managers m join t_mgr_role mr on m.mgr_id=mr.mgr_id join t_roles r on mr.role_id=r.role_id where mr.role_id=? order by mgr_id asc";
                ps=conn.prepareStatement(sql);
                ps.setString(1,role_id);
            }
            rs=ps.executeQuery();
            while (rs.next()){
                Manager manager=new Manager();
                Role role = new Role();
                role.setRole_id(rs.getInt("role_id"));
                role.setRole_name(rs.getString("role_name"));
                role.setRole_desc(rs.getString("role_desc"));
                manager.setMgr_id(rs.getString("mgr_id"));
                manager.setLogin_name(rs.getString("login_name"));
                manager.setLogin_pwd(rs.getString("login_pwd"));
                manager.setMgr_name(rs.getString("mgr_name"));
                manager.setMgr_gender(rs.getString("mgr_gender"));
                manager.setMgr_tel(rs.getString("mgr_tel"));
                manager.setMgr_email(rs.getString("mgr_email"));
                manager.setMgr_qq(rs.getString("mgr_qq"));
                manager.setCreate_time(rs.getDate("create_time"));
                manager.setRole(role);
                managerList.add(manager);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(conn,ps,rs);
        }

        return managerList;
    }

    @Override
    public void del(int mgr_id) {
        Connection conn=null;
        PreparedStatement ps=null;
        try {
            conn=DBUtil.getConnection();
            String sql="delete from t_managers where mgr_id=?";
            ps= conn.prepareStatement(sql);
            ps.setInt(1,mgr_id);
            int count=ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(conn,ps,null);
        }
    }

    @Override
    public Manager selectById(String mgr_id) {
        Connection conn=null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        Manager manager=new Manager();
        try {
            conn= DBUtil.getConnection();
            String sql="select login_name,login_pwd,mgr_name,mgr_gender,mgr_tel,mgr_email,mgr_qq,create_time from t_managers where mgr_id=?";
            ps=conn.prepareStatement(sql);
            ps.setString(1,mgr_id);
            rs=ps.executeQuery();
            if (rs.next()){
                //将取到的值存入manager对象
                manager.setMgr_id(mgr_id);
                manager.setLogin_name(rs.getString("login_name"));
                manager.setLogin_pwd(rs.getString("login_pwd"));
                manager.setMgr_name(rs.getString("mgr_name"));
                manager.setMgr_gender(rs.getString("mgr_gender"));
                manager.setMgr_tel(rs.getString("mgr_tel"));
                manager.setMgr_email(rs.getString("mgr_email"));
                manager.setMgr_qq(rs.getString("mgr_qq"));
                manager.setCreate_time(rs.getDate("create_time"));
            }else {
                return null;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(null,ps,rs);
        }
        return manager;
    }
}
